<?php
class Sale extends CI_Model
{
	private $db_customer = 'customer.'; 
	
	public function get_info($sale_id)
	{
		$this->db->select('sales.*', FALSE);
		$this->db->select('employees.*', FALSE);
		$this->db->from('sales');
		$this->db->join('employees', 'employees.person_id = sales.customer_id', 'LEFT');
		$this->db->where('sale_id',$sale_id);
		return $this->db->get();
	}
	
	function createBill() {
		$bill_id = $this->getID() . "_" . $this->getYear () . $this->getMonth () . "_" . $this->getCount();
		return $bill_id;
	}
	
	function getDay() {
		$_day;
		$today = getdate ();
		
		if ($today ['mday'] < 10) {
			$_day = "0" . ( string ) $today ['mday'];
		} else {
			$_day = ( string ) $today ['mday'];
		}
		
		return $_day;
	}
	
	function getMonth() {
		$_month;
		$today = getdate ();
		
		if ($today ['mon'] < 10) {
			$_month = "0" . ( string ) $today ['mon'];
		} else {
			$_month = ( string ) $today ['mon'];
		}
		return $_month;
	}
	
	function getYear() {
		$_year;
		$today = getdate ();
		$_year = ( string ) $today ['year'];
		return $_year;
	}
	
	function getID() {
		return $this->Appconfig->get ( 'store_id' );
	}
	
	public function time_convert($timestamp){
		return date('Y/m/d', $timestamp);
	}
	
	function getCount() {
		$stt;
		
		$_tmpdate = $this->getYear () . '-' . $this->getMonth ();

		$this->db->from('sales');
		$this->db->like('doc', $_tmpdate);
		$count = $this->db->count_all_results();
		
		$_id = $count + 1;
		
		switch (strlen ( $_id )) {
			case 1 :
				$stt = '00000' . ( string ) $_id;
				break;
			case 2 :
				$stt = '0000' . ( string ) $_id;
				break;
			case 3 :
				$stt = '000' . ( string ) $_id;
				break;
			case 4 :
				$stt = '00' . ( string ) $_id;
				break;
			case 5 :
				$stt = '0' . ( string ) $_id;
				break;
			case 6 :
				$stt = ( string ) $_id;
				break;
		}
		
		return $stt;
	}

	function exists($sale_id)
	{
		$this->db->from('sales');
		$this->db->where('sale_id',$sale_id);
		$query = $this->db->get();

		return ($query->num_rows()==1);
	}
	
	function update($sale_data, $sale_id)
	{
		$this->db->where('sale_id', $sale_id);
		$success = ($this->db->update('sales',$sale_data) && $this->db->update($this->db_customer.'sales',$sale_data));
		
		return $success;
	}
	
	function save ($items,$customer_id,$employee_id,$payments,$point,$sale_id=false)
	{
		if(count($items)==0)
			return -1;

		//Alain Multiple payments
		//Build payment types string
		$payment_types='';
		foreach($payments as $payment_id=>$payment)
		{
			$payment_types=$payment_types.$payment['payment_type'].': '.to_currency($payment['payment_amount']).'<br />';
		}
		
		$sale_id = $this->Sale->createBill();
		$sales_data = array(
			'sale_id' => $sale_id,
			'sale_time' => date('Y-m-d H:i:s'),
			'doc' => date('Y-m-d'),
			'account_number' => $this->Customer->exists($customer_id) ? $this->Customer->get_info($customer_id)->account_number : null,
			'customer_id' => $this->Customer->exists($customer_id) ? $this->Customer->get_info($customer_id)->person_id : null,
			'employee_id'=>$employee_id,
			'payment_type'=>$payment_types,
			'point' => $point
		);

		//Run these queries as a transaction, we want to make sure we do all or nothing
		$this->db->trans_start();

		$this->db->insert('sales',$sales_data);
		$this->db->insert($this->db_customer.'sales',$sales_data);

		foreach($payments as $payment_id=>$payment)
		{
			if ($payment['payment_type'] == 'Point') {
				$point_used = ($payment['payment_amount'] / $this->Appconfig->get('convert_rate'));
				$this->Customer->subtract_point($point_used, $customer_id);
			}
			
			$sales_payments_data = array
			(
				'sale_id'=>$sale_id,
				'payment_type'=>$payment['payment_type'],
				'payment_amount'=>$payment['payment_amount']
			);
			$this->db->insert('sales_payments',$sales_payments_data);
		}

		foreach($items as $line=>$item)
		{
			$cur_item_info = $this->Item->get_info($item['item_id']);

			$sales_items_data = array
			(
				'sale_id'=>$sale_id,
				'item_id'=>$item['item_id'],
				'line'=>$item['line'],
				'quantity_purchased'=>$item['quantity'],
				'item_cost_price' => $cur_item_info->cost_price,
				'item_unit_price'=>$item['price']
			);

			$this->db->insert('sales_items',$sales_items_data);

			$customer = $this->Customer->get_info($customer_id);
			
			$this->Customer->add_point($point, $customer_id);
			$this->Item->subtract_quantity($item['quantity'], $item['item_id']);
		}
		$this->db->trans_complete();
		
		if ($this->db->trans_status() === FALSE)
		{
			return -1;
		}
		
		return $sale_id;
	}
	
	function delete_list($sale_ids, $employee_id,$update_inventory=TRUE) {
		$result = TRUE;
		foreach($sale_ids as $sale_id) {
			$result &= $this->delete($sale_id, $employee_id, $update_inventory);
		}
		return $result;
	}
	
	function delete($sale_id,$employee_id,$update_inventory=TRUE) {
		// start a transaction to assure data integrity
		$this->db->trans_start();
		// first delete all payments
		$this->db->delete('sales_payments', array('sale_id' => $sale_id));
		if ($update_inventory) {
			// defect, not all item deletions will be undone??
			// get array with all the items involved in the sale to update the inventory tracking
			$items = $this->get_sale_items($sale_id)->result_array();
			foreach($items as $item) {
				// create query to update inventory tracking
				$inv_data = array
				(
						'trans_date'=>date('Y-m-d H:i:s'),
						'trans_items'=>$item['item_id'],
						'trans_user'=>$employee_id,
						'trans_comment'=>'Deleting sale ' . $sale_id,
						'trans_inventory'=>$item['quantity_purchased']
	
				);
				// update inventory
				$this->Inventory->insert($inv_data);
			}
		}
		// delete all items
		$this->db->delete('sales_items', array('sale_id' => $sale_id));
		// delete sale itself
		$this->db->delete('sales', array('sale_id' => $sale_id));
		// execute transaction
		$this->db->trans_complete();
	
		return $this->db->trans_status();
	}

	function get_sale_items($sale_id)
	{
		$this->db->from('sales_items');
		$this->db->where('sale_id',$sale_id);
		return $this->db->get();
	}

	function get_sale_payments($sale_id)
	{
		$this->db->from('sales_payments');
		$this->db->where('sale_id',$sale_id);
		return $this->db->get();
	}

	function get_customer($sale_id)
	{
		$this->db->from('sales');
		$this->db->where('sale_id',$sale_id);
		return $this->Customer->get_info($this->db->get()->row()->customer_id);
	}

	//We create a temp table that allows us to do easy report/sales queries
	public function create_sales_items_temp_table()
	{
		$this->db->query("DROP TABLE IF EXISTS sales_items_temp");
		$this->db->query("CREATE TEMPORARY TABLE sales_items_temp 
				(SELECT date(sale_time) as sale_date, sale_time, 
				sales_items.sale_id, comment,
				payments.payment_type, customer_id, employee_id, 
				items.item_id, quantity_purchased, item_cost_price, item_unit_price,
				(item_unit_price*quantity_purchased) as subtotal,
				sales_items.line as line,
		(item_unit_price*quantity_purchased) as total
		FROM sales_items
		INNER JOIN sales ON sales_items.sale_id=sales.sale_id
		INNER JOIN items ON sales_items.item_id=items.item_id
		INNER JOIN (SELECT sale_id, SUM(payment_amount) AS sale_payment_amount, 
		GROUP_CONCAT(payment_type SEPARATOR ', ') AS payment_type FROM sales_payments 
		GROUP BY sale_id) AS payments 
		ON sales_items.sale_id = payments.sale_id
		GROUP BY sale_id, item_id, line)");

		//Update null subtotals to be equal to the total as these don't have tax
		$this->db->query('UPDATE '.$this->db->dbprefix('sales_items_temp'). ' SET total=subtotal WHERE total IS NULL');
	}	
	
	public function get_giftcard_value( $giftcardNumber )
	{
		if ( !$this->Giftcard->exists( $this->Giftcard->get_giftcard_id($giftcardNumber)))
			return 0;
		
		$this->db->from('giftcards');
		$this->db->where('giftcard_number',$giftcardNumber);
		return $this->db->get()->row()->value;
	}
}
?>
